﻿/******************************************************/
// DALRice データアクセスクラス  
/******************************************************/

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System;

namespace MAA.Data
{
    /// <summary>
    /// 米を参照のデータベースへのプロセスデータアクセス 
    /// </summary>
    public class DALRice
    {
        private string conStr = ConfigurationManager.AppSettings["connectionString"];

        /// <summary>
        /// すべてのお米の種類の情報を取得します 
        /// </summary>
        /// <returns></returns>
        public DataTable getRice(string lang,int pageIndex, int pageSize, out long rowNum)
        {
            DataTable tblRice = new DataTable();
            rowNum = 0;

            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd;
            cmd = new SqlCommand("GetRice", conn);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@Lang", SqlDbType.VarChar).Value = lang;
            cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
            cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;

            cmd.Parameters.Add("@RecordCount", SqlDbType.BigInt);
            cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(tblRice);
            rowNum = Convert.ToInt64(cmd.Parameters["@RecordCount"].Value);
            conn.Close();

            return tblRice;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="riceID"></param>
        /// <returns></returns>
        public DataTable getRiceRandom(string lang, int riceID)
        {
            DataTable riceDataTable = new DataTable();
            SqlDataAdapter riceAdapter = new SqlDataAdapter();
            riceAdapter.SelectCommand = new SqlCommand();

            if (lang.Equals("vi"))
            {
                riceAdapter.SelectCommand.CommandText = @"
                SELECT		TOP 4
                            Rice.ID,
			                Rice.TypeName,
			                Rice.Origin,
			                Rice.[Description],
			                Rice.[Image]
                FROM		Rice
                WHERE       Rice.ID != @riceID
                ORDER BY    NEWID()";
            }
            else
            {
                riceAdapter.SelectCommand.CommandText = @"
                SELECT		TOP 4
                            Rice.ID,
			                Rice.TypeNameJP AS TypeName,
			                Rice.OriginJP AS Origin,
			                Rice.[DescriptionJP] AS [Description],
			                Rice.[Image]
                FROM		Rice
                WHERE       Rice.ID != @riceID
                ORDER BY    NEWID()";
            }

            riceAdapter.SelectCommand.Parameters.Add("@riceID", SqlDbType.Int).Value = riceID;
            riceAdapter.SelectCommand.Connection = new SqlConnection(conStr);
            riceAdapter.Fill(riceDataTable);
            riceAdapter.SelectCommand.Connection.Close();

            return riceDataTable;
        }

        /// <summary>
        /// お米の種類の詳細な情報を取得します 
        /// </summary>
        /// <param name="riceID"></param>
        /// <returns></returns>
        public DataTable getRiceInfo(string lang, int riceID)
        {
            DataTable riceDataTable = new DataTable();
            SqlDataAdapter riceAdapter = new SqlDataAdapter();
            riceAdapter.SelectCommand = new SqlCommand();

            if (lang.Equals("vi"))
            {
                riceAdapter.SelectCommand.CommandText = @"
                SELECT		Rice.TypeName,
			                Rice.Origin,
			                Rice.[Description],
			                Rice.[Image],
			                [Admin].Name
                FROM		Rice, [Admin]
                WHERE		[Admin].ID = Rice.AdminID
	                AND		Rice.ID = @riceID";
            }
            else
            {
                riceAdapter.SelectCommand.CommandText = @"
                SELECT		Rice.TypeNameJP AS TypeName,
			                Rice.OriginJP AS Origin,
			                Rice.[DescriptionJP] AS [Description],
			                Rice.[Image],
			                [Admin].Name
                FROM		Rice, [Admin]
                WHERE		[Admin].ID = Rice.AdminID
	                AND		Rice.ID = @riceID";
            }
            riceAdapter.SelectCommand.Parameters.Add("@riceID", SqlDbType.Int).Value = riceID;

            riceAdapter.SelectCommand.Connection = new SqlConnection(conStr);
            riceAdapter.Fill(riceDataTable);
            riceAdapter.SelectCommand.Connection.Close();

            return riceDataTable;
        }

        /// <summary>
        /// すべてのお米の種類の市場価格を得る 
        /// </summary>
        /// <returns></returns>
        public DataTable getRicePrice(string lang)
        {
            DataTable tblRicePrice = new DataTable();
            string sqlCreate;
            // String query to get price of all rice type in warehouse
            if (lang.Equals("vi"))
            {
                sqlCreate = @"SELECT     Rice.TypeName AS RiceType,
                                            Rice.OldPrice,
                                            Rice.CurrentPrice AS UpdatePrice,
                                            CONVERT(varchar, Rice.CurrentDate, 105) AS UpdateTime
                                FROM        Rice 
                                WHERE       Rice.CurrentPrice != 0";
            }
            else
            {
                sqlCreate = @"SELECT     Rice.TypeNameJP AS RiceType,
                                            Rice.OldPrice,
                                            Rice.CurrentPrice AS UpdatePrice,
                                            CONVERT(varchar, Rice.CurrentDate, 105) AS UpdateTime
                                FROM        Rice 
                                WHERE       Rice.CurrentPrice != 0";
            }
            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand(sqlCreate, conn);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(tblRicePrice);
            conn.Close();

            return tblRicePrice;
        }

        /// <summary>
        /// お米の種類の市場価格を得る 
        /// </summary>
        /// <param name="riceID"></param>
        /// <returns></returns>
        public DataTable getRicePrice(int riceID)
        {
            DataTable riceDataTable = new DataTable();
            SqlDataAdapter riceAdapter = new SqlDataAdapter();
            riceAdapter.SelectCommand = new SqlCommand();

            riceAdapter.SelectCommand.CommandText = @"
                SELECT		Rice.CurrentPrice,
			                Rice.PercentPrice
                FROM		Rice
                WHERE		Rice.ID = @riceID";

            riceAdapter.SelectCommand.Parameters.Add("@riceID", SqlDbType.Int).Value = riceID;

            riceAdapter.SelectCommand.Connection = new SqlConnection(conStr);
            riceAdapter.Fill(riceDataTable);
            riceAdapter.SelectCommand.Connection.Close();

            return riceDataTable;
        }

        /// <summary>
        /// すべてのお米の種類の名前を取得する 
        /// </summary>
        /// <returns></returns>
        public DataTable getRiceType(string lang)
        {
            DataTable tblRiceType = new DataTable();
            string sqlCreate;
            // String query to get all rice type in warehouse
            if (lang.Equals("vi"))
            {
                sqlCreate = @"   SELECT Rice.ID, Rice.TypeName
                                    FROM Rice";
            }
            else
            {
                sqlCreate = @"   SELECT Rice.ID, Rice.TypeNameJP AS TypeName
                                    FROM Rice";
            }
            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand(sqlCreate, conn);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(tblRiceType);
            conn.Close();

            return tblRiceType;
        }

        /// <summary>
        /// すべてのお米の種類の名前は、そのユーザーが倉庫に追加することができます取得 
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public DataTable getRiceTypeToAdd(string lang,long userID)
        {
            DataTable tblTypeAdd = new DataTable();
            string sqlAdd;
            // String query to get rice type not in warehouse
            if (lang.Equals("vi"))
            {
                sqlAdd = @"  SELECT Rice.ID, Rice.TypeName
                                FROM Rice
                                WHERE Rice.ID NOT IN
                                    (
                                        SELECT  WareHouse.RiceID
                                        FROM    WareHouse
                                        WHERE   WareHouse.UserID = @userID
                                    )";
            }
            else
            {
                sqlAdd = @"  SELECT Rice.ID, Rice.TypeNameJP AS TypeName
                                FROM Rice
                                WHERE Rice.ID NOT IN
                                    (
                                        SELECT  WareHouse.RiceID
                                        FROM    WareHouse
                                        WHERE   WareHouse.UserID = @userID
                                    )";
            }
            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand(sqlAdd, conn);

            cmd.Parameters.Add("@userID", SqlDbType.BigInt).Value = userID;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(tblTypeAdd);
            conn.Close();

            return tblTypeAdd;
        }

        /// <summary>
        /// ユーザーが販売して投稿を作成するために使用できるすべての米の種類の名前を取得する
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public DataTable getRiceTypeToCreate(string lang, long userID)
        {
            DataTable tblTypeCreate = new DataTable();
            string sqlCreate;
            // String query to get rice type in warehouse
            if (lang.Equals("vi"))
            {
                sqlCreate = @"  SELECT Rice.ID, Rice.TypeName
                                FROM Rice
                                WHERE Rice.ID IN
                                    (
                                        SELECT  WareHouse.RiceID
                                        FROM    WareHouse
                                        WHERE   WareHouse.UserID = @userID
                                    )";
            }
            else
            {
                sqlCreate = @"  SELECT Rice.ID, Rice.TypeNameJP AS TypeName
                                FROM Rice
                                WHERE Rice.ID IN
                                    (
                                        SELECT  WareHouse.RiceID
                                        FROM    WareHouse
                                        WHERE   WareHouse.UserID = @userID
                                    )";
            }
            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand(sqlCreate, conn);

            cmd.Parameters.Add("@userID", SqlDbType.BigInt).Value = userID;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(tblTypeCreate);
            conn.Close();

            return tblTypeCreate;
        }
    }
}